package com.freeter.modules.excel;


import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;

/**
 * @Title ExeclTools.java
 *
 * @Description excel工具
 *
 * @author ladybug QQ: 3204644918
 *
 * @date 2015年4月14日下午3:43:54
 *
 * @Package com.ladybug.biz.common.tools
 *
 */
public class ExcelTools {

    /**
     *
     * <p>
     * Title:
     * </p>
     * <p>
     * Description:从json数组中解析出java字符串数组
     * </p>
     *
     * @author jacobliang
     * @param jsonString
     * @return
     */
//	public static String[] getStringArray4Json(String jsonString) {
//
//		JSONArray jsonArray = JSONArray.fromObject(jsonString);
//		String[] stringArray = new String[jsonArray.size()];
//		for (int i = 0; i < jsonArray.size(); i++) {
//			stringArray[i] = jsonArray.getString(i);
//
//		}
//
//		return stringArray;
//	}

    /**
     * 根据List对象导出 execl
     *
     * @param <T>
     * @param execlhearList
     *            表头
     * @param excelData
     *            表索引
     * @param sheetName
     *            表格名(英文)
     * @param pojoList
     *            导出值
     * @return
     */
    public static <T> HSSFWorkbook execlExport(List<String[]> execlhearList,
                                               String[] excelData, String sheetName, List<T> pojoList) {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        wb.setSheetName(0, sheetName);
        // String hearString = "";
        // String excelDataString = "";
        // try {
        // hearString = java.net.URLDecoder.decode(hear, "utf-8");
        // excelDataString = java.net.URLDecoder.decode(data, "utf-8");
        // } catch (UnsupportedEncodingException e) {
        // e.printStackTrace();
        // }
        // String[] execlhear = getStringArray4Json(hearString);
        // String[] excelData = getStringArray4Json(excelDataString);
        HSSFCell cell;
        // 表头
        HSSFRow row ;
        int execlHearSize = execlhearList.size();
        for (int i = 0; i < execlHearSize; i++) {
            String[] execlhear = execlhearList.get(i);
            row = sheet.createRow((int) i);
            int tempStringLength = execlhear.length;
            for (int a = 0; a < tempStringLength; a++) {
                cell = row.createCell(a);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(execlhear[a]);
            }
        }
        int dataStringLength = excelData.length;
        int count = pojoList.size();

        int a = 0;

        HSSFCellStyle cellStyle = wb.createCellStyle(); // 建立新的cell样式
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

        // 写实体
        for (int i = 0; i < count; i++) {
            Object temp = pojoList.get(i);
            row = sheet.createRow((int) i + execlHearSize);
            a = 0;

            if (temp instanceof Map) {
                Map map = (Map) temp;

                for (int dataInt = 0; dataInt < dataStringLength; dataInt++) {
                    cell = row.createCell( a++);
                    Object obj = map.get(excelData[dataInt]);
                    // 值是字符串
                    if (obj instanceof String) {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue((String) obj);

                        // 值为数字
                    } else if (obj instanceof BigDecimal) {
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(((BigDecimal) obj).doubleValue());

                        // 值为日期
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                        cell.setCellStyle(cellStyle);
                    } else {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(String.valueOf(obj));
                    }

                }

            } else { // List<pojo> 处理
                Class c = temp.getClass();
                // 获取POJO所有方法
                Method m[] = c.getDeclaredMethods();

                for (int dataInt = 0; dataInt < dataStringLength; dataInt++) {
                    cell = row.createCell((short) a++);
                    // 数据索引
                    String dataIndex = excelData[dataInt];

                    String methodString = "get"
                            + dataIndex.substring(0, 1).toUpperCase()
                            + dataIndex.substring(1, dataIndex.length());
                    // 根据循环获取索引值
                    for (int k = 0; k < m.length; k++) {
                        if (m[k].getName().equals(methodString)) {
                            // System.out.println("方法名："+m[i].getName());
                            try {
                                Object obj = m[k].invoke(temp);
                                // 值是字符串
                                if (obj instanceof String) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cell.setCellValue((String) obj);

                                    // 值为数字
                                } else if (obj instanceof BigDecimal) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue(((BigDecimal) obj)
                                            .doubleValue());

                                } else if (obj instanceof Long) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue(((Long) obj));

                                    // 值为日期
                                } else if (obj instanceof Date) {
                                    cell.setCellValue((Date) obj);
                                    cell.setCellStyle(cellStyle);
                                } else if (obj instanceof Double) {
                                    cell.setCellValue((Double) obj);
                                    cell.setCellStyle(cellStyle);
                                } else {
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cell.setCellValue(String.valueOf(obj));
                                }

                                break;
                            } catch (IllegalArgumentException e) {
                                e.printStackTrace();
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            } catch (InvocationTargetException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }

        // row = sheet.createRow(count + 1);
        // cell = row.createCell((short) 0);
        // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        // cell.setCellValue("当前导出" + count + "条记录，总共" + count + "条记录");

        return wb;

    }


    /**
     * 根据List对象导出 execl
     * @param execlhearList
     * @param excelData
     * @param pojoList
     * @param wb
     * @param <T>
     * @return
     */
    public static <T> Workbook execlExportWithTemplate(List<String[]> execlhearList,
                                                       String[] excelData,
                                                       List<?> pojoList,
                                                       Workbook wb) {

        Sheet sheet = wb.getSheetAt(0);
        Cell cell;
        // 表头
        Row row ;
        int execlHearSize = 1;

        int dataStringLength = excelData.length;
        int count = pojoList.size();

        int a = 0;

        CellStyle cellStyle = wb.createCellStyle(); // 建立新的cell样式
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

        // 写实体
        for (int i = 0; i < count; i++) {
            Object temp = pojoList.get(i);
            row = sheet.createRow((int) i + execlHearSize);
            a = 0;

            if (temp instanceof Map) {
                Map map = (Map) temp;

                for (int dataInt = 0; dataInt < dataStringLength; dataInt++) {
                    cell = row.createCell( a++);
                    Object obj = map.get(excelData[dataInt]);
                    // 值是字符串
                    if (obj instanceof String) {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue((String) obj);

                        // 值为数字
                    } else if (obj instanceof BigDecimal) {
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(((BigDecimal) obj).doubleValue());

                        // 值为日期
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                        cell.setCellStyle(cellStyle);
                    } else {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(String.valueOf(obj));
                    }

                }

            } else { // List<pojo> 处理
                Class c = temp.getClass();
                // 获取POJO所有方法
                Method m[] = c.getDeclaredMethods();

                for (int dataInt = 0; dataInt < dataStringLength; dataInt++) {
                    cell = row.createCell((short) a++);
                    // 数据索引
                    String dataIndex = excelData[dataInt];

                    String methodString = "get"
                            + dataIndex.substring(0, 1).toUpperCase()
                            + dataIndex.substring(1, dataIndex.length());
                    // 根据循环获取索引值
                    for (int k = 0; k < m.length; k++) {
                        if (m[k].getName().equals(methodString)) {
                            // System.out.println("方法名："+m[i].getName());
                            try {
                                Object obj = m[k].invoke(temp);
                                // 值是字符串
                                if (obj instanceof String) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cell.setCellValue((String) obj);

                                    // 值为数字
                                } else if (obj instanceof BigDecimal) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue(((BigDecimal) obj)
                                            .doubleValue());

                                } else if (obj instanceof Long) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue(((Long) obj));

                                    // 值为日期
                                } else if (obj instanceof Date) {
                                    cell.setCellValue((Date) obj);
                                    cell.setCellStyle(cellStyle);
                                } else if (obj instanceof Double) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue((Double) obj);
                                } else {
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    if(null != obj){
                                        cell.setCellValue(String.valueOf(obj));
                                    }else{
                                        cell.setCellValue("");
                                    }
                                }

                                break;
                            } catch (IllegalArgumentException e) {
                                e.printStackTrace();
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            } catch (InvocationTargetException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }

        // row = sheet.createRow(count + 1);
        // cell = row.createCell((short) 0);
        // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        // cell.setCellValue("当前导出" + count + "条记录，总共" + count + "条记录");

        return wb;

    }


    /**
     * 根据List对象导出 execl
     *
     * @param <T>
     * @param execlhearList
     *            表头
     * @param excelData
     *            表索引
     * @param sheetName
     *            表格名(英文)
     * @param pojoList
     *            导出值
     * @return
     */
    public static <T> XSSFWorkbook execlExportXSSFWorkbook(List<String[]> execlhearList,
                                                           String[] excelData, String sheetName, List<T> pojoList) {

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(sheetName);
        wb.setSheetName(0, sheetName);
        XSSFCell cell;
        // 表头
        XSSFRow row ;
        int execlHearSize = execlhearList.size();
        for (int i = 0; i < execlHearSize; i++) {
            String[] execlhear = execlhearList.get(i);
            row = sheet.createRow((int) i);
            int tempStringLength = execlhear.length;
            for (int a = 0; a < tempStringLength; a++) {
                cell = row.createCell(a);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(execlhear[a]);
            }
        }
        int dataStringLength = excelData.length;
        int count = pojoList.size();

        int a = 0;

        XSSFCellStyle cellStyle = wb.createCellStyle(); // 建立新的cell样式
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

        // 写实体
        for (int i = 0; i < count; i++) {
            Object temp = pojoList.get(i);
            row = sheet.createRow((int) i + execlHearSize);
            a = 0;

            if (temp instanceof Map) {
                Map map = (Map) temp;

                for (int dataInt = 0; dataInt < dataStringLength; dataInt++) {
                    cell = row.createCell( a++);
                    Object obj = map.get(excelData[dataInt]);
                    // 值是字符串
                    if (obj instanceof String) {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue((String) obj);

                        // 值为数字
                    } else if (obj instanceof BigDecimal) {
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(((BigDecimal) obj).doubleValue());

                        // 值为日期
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                        cell.setCellStyle(cellStyle);
                    } else {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(String.valueOf(obj));
                    }

                }

            } else { // List<pojo> 处理
                Class c = temp.getClass();
                // 获取POJO所有方法
                Method m[] = c.getDeclaredMethods();

                for (int dataInt = 0; dataInt < dataStringLength; dataInt++) {
                    cell = row.createCell((short) a++);
                    // 数据索引
                    String dataIndex = excelData[dataInt];

                    String methodString = "get"
                            + dataIndex.substring(0, 1).toUpperCase()
                            + dataIndex.substring(1, dataIndex.length());
                    // 根据循环获取索引值
                    for (int k = 0; k < m.length; k++) {
                        if (m[k].getName().equals(methodString)) {
                            // System.out.println("方法名："+m[i].getName());
                            try {
                                Object obj = m[k].invoke(temp);
                                // 值是字符串
                                if (obj instanceof String) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cell.setCellValue((String) obj);

                                    // 值为数字
                                } else if (obj instanceof BigDecimal) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue(((BigDecimal) obj)
                                            .doubleValue());

                                } else if (obj instanceof Long) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue(((Long) obj));

                                    // 值为日期
                                } else if (obj instanceof Date) {
                                    cell.setCellValue((Date) obj);
                                    cell.setCellStyle(cellStyle);
                                } else if (obj instanceof Double) {
                                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                    cell.setCellValue((Double) obj);
                                } else {
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    if(null != obj){
                                        cell.setCellValue(String.valueOf(obj));
                                    }else{
                                        cell.setCellValue("");
                                    }
                                }

                                break;
                            } catch (IllegalArgumentException e) {
                                e.printStackTrace();
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            } catch (InvocationTargetException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }

        // row = sheet.createRow(count + 1);
        // cell = row.createCell((short) 0);
        // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        // cell.setCellValue("当前导出" + count + "条记录，总共" + count + "条记录");

        return wb;

    }


    /**
     * 日对账单导出
     * @param sheetName
     * @param totalData
     * @param totalPosition
     * @param pojoStartRowIdx
     * @param pojoColNames
     * @param pojoList
     * @param wb
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> XSSFWorkbook exportShopDailyReport(String sheetName,
                                                         Map<String,Object>  totalData,
                                                         Map<String,CellPosition> totalPosition,
                                                         int pojoStartRowIdx,
                                                         String[] pojoColNames,
                                                         List<Map<String,Object>> pojoList,
                                                         XSSFWorkbook wb) throws Exception {

        XSSFSheet sheet = wb.getSheetAt(0);
        wb.setSheetName(0, sheetName);
        sheet.getRow(0).getCell(0).setCellValue(sheetName);
        // 汇总
        for (Map.Entry<String,Object> entry : totalData.entrySet()) {
            CellPosition cellPosition = totalPosition.get(entry.getKey());
            if(cellPosition == null){ continue; }
            XSSFRow row1 = sheet.getRow(cellPosition.getRowIdx());
            XSSFCell cell1 = row1.getCell(cellPosition.getColIdx());
            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cell1.setCellStyle(cellStyle);
            setCell(cell1,entry.getValue());
        }

        //列表导出项目明细行
        XSSFRow sourceRow = sheet.getRow(2);
        XSSFRow row = null;XSSFCell cell;
        for (int i = 0; i < pojoList.size(); i++) {//订单循环
            List temp =  pojoList.get(i) == null? Collections.EMPTY_LIST:(List) pojoList.get(i);
            for(int j =0 ; j< temp.size();j++) {//订单行循环
                Object cells = temp.get(j);
                if (sheet.getRow(pojoStartRowIdx) != null) {
                    int lastRowNo = sheet.getLastRowNum();
                    sheet.shiftRows(pojoStartRowIdx, lastRowNo, 1,true,false);
                }
                row = sheet.createRow(pojoStartRowIdx++);
                row.setHeight(sourceRow.getHeight());
                //填写row上的单元格
                for (int dataInt = 0; dataInt < pojoColNames.length; dataInt++) {
                    cell = row.createCell(dataInt);
                    XSSFCell sourceCell = sourceRow.getCell(dataInt);
                    cell.setCellType(sourceCell.getCellType());
                    cell.setCellStyle(sourceCell.getCellStyle());
                    //单元格上的值
                    String pojoColName = pojoColNames[dataInt];
                    Object cellVal = getCellVal(cells, pojoColName);
                    setCell(cell, cellVal);
                }
            }
        }
        CellRangeAddress region = new CellRangeAddress(pojoStartRowIdx+11, pojoStartRowIdx+11, 1, 16);
        sheet.addMergedRegion(region);
        CellRangeAddress region1 = new CellRangeAddress(pojoStartRowIdx+12, pojoStartRowIdx+12, 0, 16);
        sheet.addMergedRegion(region1);
        return wb;
    }

    private static Object getCellVal(Object temp, String pojoColName) throws InvocationTargetException, IllegalAccessException {
        if (temp instanceof Map||temp instanceof HashMap) {
            Map map = (Map) temp;
            return map.get(pojoColName);
        } else {
            Method declaredMethod = BeanUtils.findDeclaredMethod(temp.getClass(), convertGetMethod(pojoColName));
            return declaredMethod.invoke(temp);
        }
    }

    private static String convertGetMethod(String pojoColName) {
        String firstChar = String.valueOf(pojoColName.charAt(0)).toUpperCase();
        pojoColName.substring(1);
        StringBuffer sb = new StringBuffer();
        sb.append("get");
        sb.append(firstChar).append(pojoColName.substring(1));
        return sb.toString();
    }

    private static void setCell(XSSFCell cell, Object obj) {

        if(null == obj){
            cell.setCellValue("");
            return;
        }

        if (obj instanceof String) {// 值是字符串
            cell.setCellValue((String) obj);
        } else if (obj instanceof BigDecimal) {// 值为数字
            cell.setCellValue(((BigDecimal) obj).doubleValue());
        } else if (obj instanceof Date) {// 值为日期
            cell.setCellValue((Date) obj);
            //XSSFCellStyle cellStyle = wb.createCellStyle(); // 建立新的cell样式
            //cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
            //cell.setCellStyle(cellStyle);
        } else {
            cell.setCellValue(String.valueOf(obj));
        }
    }

    /**
     * 读取单元格的数据
     *
     * @param row
     * @param cell
     * @return
     * @throws NullPointerException
     */
    public static String processCellValue(XSSFRow row, int cell)
            throws NullPointerException {
        XSSFCell headCell = row.getCell( cell);

        if (null == headCell) {
            return "";
        }

        String itemName = "";
        if (HSSFCell.CELL_TYPE_STRING ==  headCell.getCellType()) {
            itemName = headCell.getStringCellValue();
        }

        if (HSSFCell.CELL_TYPE_NUMERIC == headCell.getCellType()) {
            if (HSSFDateUtil.isCellDateFormatted(headCell)) {
                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                double d = headCell.getNumericCellValue();
                Date date = HSSFDateUtil.getJavaDate(d);
                itemName = formater.format(date);
            } else {
                BigDecimal a = new BigDecimal(String.valueOf(headCell
                        .getNumericCellValue()));
                itemName = (a.setScale(6, BigDecimal.ROUND_HALF_UP)).toString();
            }
        }

        if (HSSFCell.CELL_TYPE_BOOLEAN == headCell.getCellType()) {
            itemName = String.valueOf(headCell.getBooleanCellValue());
        }

        if (HSSFCell.CELL_TYPE_BLANK == headCell.getCellType()) {
            itemName = "";
        }

        if (HSSFCell.CELL_TYPE_FORMULA == headCell.getCellType()) {
            itemName = headCell.getCellFormula();
        }
        return itemName;
    }

    /**
     * 读取单元格的数据
     *
     * @param row
     * @param cell
     * @return
     * @throws NullPointerException
     */
    public static String processCellValue(HSSFRow row, int cell)
            throws NullPointerException {
        Cell headCell = row.getCell( cell);

        if (null == headCell) {
            return "";
        }

        String itemName = "";
        if (HSSFCell.CELL_TYPE_STRING == headCell.getCellType()) {
            itemName = headCell.getStringCellValue();
        }

        if (HSSFCell.CELL_TYPE_NUMERIC == headCell.getCellType()) {
            if (HSSFDateUtil.isCellDateFormatted(headCell)) {
                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                double d = headCell.getNumericCellValue();
                Date date = HSSFDateUtil.getJavaDate(d);
                itemName = formater.format(date);
            } else {
                BigDecimal a = new BigDecimal(String.valueOf(headCell
                        .getNumericCellValue()));
                itemName = (a.setScale(6, BigDecimal.ROUND_HALF_UP)).toString();
            }
        }

        if (HSSFCell.CELL_TYPE_BOOLEAN == headCell.getCellType()) {
            itemName = String.valueOf(headCell.getBooleanCellValue());
        }

        if (HSSFCell.CELL_TYPE_BLANK == headCell.getCellType()) {
            itemName = "";
        }

        if (HSSFCell.CELL_TYPE_FORMULA == headCell.getCellType()) {
            itemName = headCell.getCellFormula();

            try {
                itemName = String.valueOf(headCell.getNumericCellValue());
            } catch (IllegalStateException e) {
                itemName = String.valueOf(headCell.getRichStringCellValue());
            }

        }
        return itemName;
    }


    /**
     * 读取单元格的数据
     *
     * @param row
     * @param cell
     * @return
     * @throws NullPointerException
     */
    public static String processCellValue(Row row, int cell)throws NullPointerException {

        if(row instanceof HSSFRow) {
            return processCellValue((HSSFRow)row, cell);
        }

        if(row instanceof XSSFRow) {
            return processCellValue((XSSFRow)row, cell);
        }

        throw new RuntimeException("不支持的excel格式");
    }

    public static byte[] export(
            List<?> datas,
            String[] headers,
            String[] colNames)  {
        Workbook wb = null;
        try {
            Long time = System.currentTimeMillis();
            //生产excel对象，并序列化到字节数组缓存
            wb = new XSSFWorkbook(ExcelTools.class.getResourceAsStream("/template/shop.xlsx"));
//            wb = ExcelTools.execlExportWithTemplate(Collections.EMPTY_LIST,
//                    colNames,
//                    datas,
//                    wb);
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            //将excel文件写入输出流
            wb.write(out);
            time = System.currentTimeMillis() - time;
            //File f = new File("d:/ft.xlsx");
            //IOUtils.write(out.toByteArray(),new FileOutputStream(f));
            //out.close();

            return out.toByteArray();
            //return null;

        }catch (Exception e) {
            throw new RuntimeException("系统繁忙，请稍后再试");
        } finally {
            if(wb != null){
                try {
                    wb.close();
                } catch (IOException e) {
                    System.out.println("导出数据，workbook close 出错"+ e.getMessage());
                }
            }
        }
    }

    public static <T> XSSFWorkbook execlExportXSSFWorkbook(ExportBean exportBean,
                                                       XSSFWorkbook wb) throws Exception {


        String sheetName = exportBean.getSheetName();
        Integer pojoStartRowIdx = exportBean.getPojoStartRowIdx();
        String[] pojoColNames = exportBean.getDd();
        List<T> pojoList=exportBean.getPojoList();

        XSSFSheet sheet = wb.getSheetAt(0);
        wb.setSheetName(0, sheetName);
        sheet.getRow(1).getCell(0).setCellValue(exportBean.getQueryString());

        //列表导出项目明细行
        XSSFRow sourceRow = sheet.getRow(pojoStartRowIdx);
        XSSFRow row = null;
        XSSFCell cell;
        //行记录循环
        for (int i = 0; i < pojoList.size(); i++) {
            Object cells = pojoList.get(i) ;
            if (sheet.getRow(pojoStartRowIdx) != null) {
                int lastRowNo = sheet.getLastRowNum();
                sheet.shiftRows(pojoStartRowIdx, lastRowNo, 1, true, false);
            }
            row = sheet.createRow(pojoStartRowIdx++);
            row.setHeight(sourceRow.getHeight());
            //填写row上的单元格
            for (int dataInt = 0; dataInt < pojoColNames.length; dataInt++) {
                cell = row.createCell(dataInt);
                XSSFCell sourceCell = sourceRow.getCell(dataInt);
                cell.setCellType(sourceCell.getCellType());
                cell.setCellStyle(sourceCell.getCellStyle());
                //单元格上的值
                String pojoColName = pojoColNames[dataInt];
                Object cellVal = getCellVal(cells, pojoColName);
                setCell(cell, cellVal);
            }
        }

        //合并
        //CellRangeAddress region = new CellRangeAddress(pojoStartRowIdx+9, pojoStartRowIdx+9, 0, 12);
        //sheet.addMergedRegion(region);
        return wb;
    }
}